########
#Important: Must follow directions below to install pacman, change working directory
########

######## Load Packages - requires pacman. Before intial use must run: install.packages("pacman") and then library(pacman)
#install.packages("pacman")
#library(pacman)

pacman::p_load(readxl,
               tidyverse,
               readstata13,
               data.table,
               survival,
               writexl)
########

######## set dir - only need to change this, others are relative paths
setwd("C:\\Users\\Sean\\Dropbox\\Eran\\Terrorism_Left")
########

######## read terror event data and merge with manually coded motives
terror_base <- read.dta13("Data_do_files\\Terror Data\\terror.dta")
motives <- read_excel("Data_do_files\\Terror Data\\motives.xlsx")
terror_new <- read_csv("Data_do_files\\Terror Data\\terror_new.csv")
terror_base <- rbind(terror_base, terror_new)
terror_base <- merge(terror_base, motives, by = "eventid", all.x = TRUE)

terror_base$weaptype1_txt[terror_base$weaptype1_txt == "Explosives/Bombs/Dynamite"] <- "Explosives"
########

###############################################
#############Presidential Data#################
###############################################

######## read data
terror <- terror_base
pres <- read.dta13("Election_datasets\\US_presidential_elections\\1972_to_2016_stata.dta")
########

######## create event and election date variables - election dates from 
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

pres <- pres %>% select(-c(county_name, county_name_2))
pres <- unique(pres)

pres$election_date[pres$year == 1972 ] <- "1972-11-07"
pres$election_date[pres$year == 1976 ] <- "1976-11-02"
pres$election_date[pres$year == 1980 ] <- "1980-11-04"
pres$election_date[pres$year == 1984 ] <- "1984-11-06"
pres$election_date[pres$year == 1988 ] <- "1988-11-08"
pres$election_date[pres$year == 1992 ] <- "1992-11-03"
pres$election_date[pres$year == 1996 ] <- "1996-11-05"
pres$election_date[pres$year == 2000 ] <- "2000-11-07"
pres$election_date[pres$year == 2004 ] <- "2004-11-02"
pres$election_date[pres$year == 2008 ] <- "2008-11-04"
pres$election_date[pres$year == 2012 ] <- "2012-11-06"
pres$election_date[pres$year == 2016 ] <- "2016-11-08"

pres$election_date <- as.Date(pres$election_date, format = "%Y-%m-%d")
########

######## Match with data on elections before and after attack
terror$t1 <- neardate(as.integer(terror$FIPSCounty), as.integer(pres$FIPS), terror$event_date, pres$election_date, best = "prior", nomatch = NA)
terror$t2 <- neardate(as.integer(terror$FIPSCounty), as.integer(pres$FIPS), terror$event_date, pres$election_date, best = "after", nomatch = NA)
########

######## merge
pres_t1 <- pres
pres_t2 <- pres

colnames(pres_t1) <- paste(colnames(pres_t1), "t1", sep = "_")
pres_t1$t1 <- seq.int(nrow(pres_t1))
colnames(pres_t2) <- paste(colnames(pres_t2), "t2", sep = "_")
pres_t2$t2 <- seq.int(nrow(pres_t2))


terror_pres <- merge(terror, pres_t1, by = "t1", all.x = TRUE)
terror_pres <- merge(terror_pres, pres_t2, by = "t2", all.x = TRUE)
########

######## generate margin of victory and difference
#terror_pres$rep_fac_1 <- NA
#terror_pres$rep_fac_1[terror_pres$rep_vote_t1 == 1] <- 1
#terror_pres$rep_fac_1[terror_pres$dem_vote_t1 == 1] <- -1
#terror_pres$rep_margin_t1 <- terror_pres$H_t1 * terror_pres$rep_fac_1

#terror_pres$rep_fac_2 <- NA
#terror_pres$rep_fac_2[terror_pres$rep_vote_t2 == 1] <- 1
#terror_pres$rep_fac_2[terror_pres$dem_vote_t2 == 1] <- -1
#terror_pres$rep_margin_t2 <- terror_pres$H_t2 * terror_pres$rep_fac_2

#terror_pres$rep_marg_change <- terror_pres$rep_margin_t2 - terror_pres$rep_margin_t1

terror_pres$event_to_t2 <- terror_pres$election_date_t2 - terror_pres$event_date
terror_pres$t1_to_event <- terror_pres$event_date - terror_pres$election_date_t1

terror_pres <- terror_pres %>% select(-c(LSAD_TRANS_t1, LSAD_TRANS_t2, FIPS_t1, FIPS_t2, county_fips_t1, county_fips_t2,
                                         state_fips_t1, state_fips_t2, state_abbrev_2_t1, state_abbrev_2_t2, state_abbrev_t1, state_abbrev_t2, t1, t2))

write_xlsx(terror_pres, "Merged_datasets\\terror_pres_wide.xlsx", col_names = TRUE)
########

###############################################
############# Senate Data #####################
###############################################

######## read data
terror <- terror_base
senate <- read.dta13("Election_datasets\\Congressional Elections\\Senate\\Senate_merged_congress.dta")
########

######## Election Dates
senate$election_date[senate$year == 1994 ] <- "1994-11-08"
senate$election_date[senate$year == 1996 ] <- "1996-11-05"
senate$election_date[senate$year == 1998 ] <- "1998-11-03"
senate$election_date[senate$year == 2000 ] <- "2000-11-07"
senate$election_date[senate$year == 2002 ] <- "2002-11-05"
senate$election_date[senate$year == 2004 ] <- "2004-11-02"
senate$election_date[senate$year == 2006 ] <- "2006-11-07"
senate$election_date[senate$year == 2008 ] <- "2008-11-04"
senate$election_date[senate$year == 2010 ] <- "2010-11-02"
senate$election_date[senate$year == 2012 ] <- "2012-11-06"
senate$election_date[senate$year == 2014 ] <- "2014-11-04"
senate$election_date[senate$year == 2016 ] <- "2016-11-08"

senate$election_date <- as.Date(senate$election_date, format = "%Y-%m-%d")
senate <- unique(senate) %>% select(-c(prev_leg_elec_year, next_leg_elec_year))
########

######## Terror Dates
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

terror <- terror %>% filter(year >= 1994)
########

######## Match with data on elections before and after attack
terror$t1 <- neardate(as.integer(terror$FIPSCounty), as.integer(senate$fips), terror$event_date, senate$election_date, best = "prior", nomatch = NA)
terror$t2 <- neardate(as.integer(terror$FIPSCounty), as.integer(senate$fips), terror$event_date, senate$election_date, best = "after", nomatch = NA)
########

######## merge
senate_t1 <- senate
senate_t2 <- senate

colnames(senate_t1) <- paste(colnames(senate_t1), "t1", sep = "_")
senate_t1$t1 <- seq.int(nrow(senate_t1))
colnames(senate_t2) <- paste(colnames(senate_t2), "t2", sep = "_")
senate_t2$t2 <- seq.int(nrow(senate_t2))


terror_senate <- merge(terror, senate_t1, by = "t1", all.x = TRUE)
terror_senate <- merge(terror_senate, senate_t2, by = "t2", all.x = TRUE)
########

######## time to and from event
terror_senate$event_to_t2 <- terror_senate$election_date_t2 - terror_senate$event_date
terror_senate$t1_to_event <- terror_senate$event_date - terror_senate$election_date_t1
########

######## write
terror_senate <- terror_senate %>% select(-c(state_fips_t1, state_fips_t2, county_fips_t1, county_fips_t2, fips_t1, fips_t2, state_abbrev_t1, state_abbrev_t2, t1, t2))
write_xlsx(terror_senate, "Merged_datasets\\terror_senate_wide.xlsx", col_names = TRUE)
########

###############################################
############# House Data ######################
###############################################

######## read data
terror <- terror_base
house <- read.dta13("Election_datasets\\Congressional Elections\\House\\House_merged_congress.dta")
########

######## Election Dates
house$election_date[house$year == 1994 ] <- "1994-11-08"
house$election_date[house$year == 1996 ] <- "1996-11-05"
house$election_date[house$year == 1998 ] <- "1998-11-03"
house$election_date[house$year == 2000 ] <- "2000-11-07"
house$election_date[house$year == 2002 ] <- "2002-11-05"
house$election_date[house$year == 2004 ] <- "2004-11-02"
house$election_date[house$year == 2006 ] <- "2006-11-07"
house$election_date[house$year == 2008 ] <- "2008-11-04"
house$election_date[house$year == 2010 ] <- "2010-11-02"
house$election_date[house$year == 2012 ] <- "2012-11-06"
house$election_date[house$year == 2014 ] <- "2014-11-04"
house$election_date[house$year == 2016 ] <- "2016-11-08"

house$election_date <- as.Date(house$election_date, format = "%Y-%m-%d")
house$expandatleft[house$expandatleft == ""] <- NA
house$county_name[!is.na(house$expandatleft)] <- house$expandatleft[!is.na(house$expandatleft)]
house <- unique(house) %>% select(-c(prev_leg_elec_year, next_leg_elec_year, expandatleft))
########

######## Terror Dates
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

terror <- terror %>% filter(year >= 1994)
########

######## Match with data on elections before and after attack
terror$t1 <- neardate(as.integer(terror$FIPSCounty), as.integer(house$fips), terror$event_date, house$election_date, best = "prior", nomatch = NA)
terror$t2 <- neardate(as.integer(terror$FIPSCounty), as.integer(house$fips), terror$event_date, house$election_date, best = "after", nomatch = NA)
########

######## merge
house_t1 <- house
house_t2 <- house

colnames(house_t1) <- paste(colnames(house_t1), "t1", sep = "_")
house_t1$t1 <- seq.int(nrow(house_t1))
colnames(house_t2) <- paste(colnames(house_t2), "t2", sep = "_")
house_t2$t2 <- seq.int(nrow(house_t2))


terror_house <- merge(terror, house_t1, by = "t1", all.x = TRUE)
terror_house <- merge(terror_house, house_t2, by = "t2", all.x = TRUE)
########

######## time to and from event
terror_house$event_to_t2 <- terror_house$election_date_t2 - terror_house$event_date
terror_house$t1_to_event <- terror_house$event_date - terror_house$election_date_t1
########

######## write
terror_house <- terror_house %>% select(-c(state_fips_t1, state_fips_t2, county_fips_t1, county_fips_t2, fips_t1, fips_t2, state_abbrev_t1, state_abbrev_t2, t1, t2))
write_xlsx(terror_house, "Merged_datasets\\terror_house_wide.xlsx", col_names = TRUE)
########


##########################################################
#############Create county-election year data ############
##########################################################

######## read data
terror <- terror_base
pres <- read.dta13("Election_datasets\\US_presidential_elections\\1972_to_2016_stata.dta")
########

######## create event and election date variables - election dates from 
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

pres <- pres %>% select(-c(county_name, county_name_2))
pres <- unique(pres)

pres$election_date[pres$year == 1972 ] <- "1972-11-07"
pres$election_date[pres$year == 1976 ] <- "1976-11-02"
pres$election_date[pres$year == 1980 ] <- "1980-11-04"
pres$election_date[pres$year == 1984 ] <- "1984-11-06"
pres$election_date[pres$year == 1988 ] <- "1988-11-08"
pres$election_date[pres$year == 1992 ] <- "1992-11-03"
pres$election_date[pres$year == 1996 ] <- "1996-11-05"
pres$election_date[pres$year == 2000 ] <- "2000-11-07"
pres$election_date[pres$year == 2004 ] <- "2004-11-02"
pres$election_date[pres$year == 2008 ] <- "2008-11-04"
pres$election_date[pres$year == 2012 ] <- "2012-11-06"
pres$election_date[pres$year == 2016 ] <- "2016-11-08"

pres$election_date <- as.Date(pres$election_date, format = "%Y-%m-%d")

election_year <- c(1972, 1976, 1980, 1984, 1988, 1992, 1996, 2000, 2004, 2008, 2012, 2016)
election_date <- c("1972-11-07", "1976-11-02", "1980-11-04", "1984-11-06", "1988-11-08", "1992-11-03", "1996-11-05", "2000-11-07", "2004-11-02", "2008-11-04", "2012-11-06", "2016-11-08")
dates <- data.frame(election_year, election_date, country = 217)

dates$election_date <- as.Date(dates$election_date, format = "%Y-%m-%d")

terror$next_election <- neardate(terror$country, dates$country, terror$event_date, dates$election_date, best = "after", nomatch = NA)

dates$next_election <- seq.int(nrow(dates))

terror <- merge(terror, dates, by = "next_election", all.x = TRUE) %>% select(-c(election_date, country.y, next_election)) %>% rename(country = country.x, attack_year = year) %>%
  mutate(attack = 1, abortion = ifelse(motive1 == "Anti-Abortion", 1, 0), hatred = ifelse(motive1 == "Hatred", 1, 0), political = ifelse(motive1 == "Political", 1, 0), unknown_motive = ifelse(motive1 == "Unknown", 1, 0))

########

pres$election_year <- pres$year

pres_terror <- merge(pres, terror, by = c("state_fips","county_fips","election_year"), all.x = TRUE)  %>% mutate_all(~na_if(., -9))

pres_terror$days_till_election <- pres_terror$election_date - pres_terror$event_date

target_merge  <- pres_terror %>% count(state_fips, county_fips, election_year, targtype1_txt) %>% spread(targtype1_txt, n, fill = 0)

targ_names <- c("state_fips","county_fips","election_year","abortion_targ_count","air_targ_count","business_targ_count","education_targ_count","foodwater_targ_count","diplo_targ_count","gov_targ_count","media_targ_count","maritime_targ_count","military_targ_count","ngo_targ_count","other_targ_count",                         
                "police_targ_count","private_targ_count","relig_targ_count","telecom_targ_count","nonstate_actor_targ_count","tourist_targ_count","transport_targ_count","unknown_targ_count","utilities_targ_count","violent_political_party_targ_count","NA_targ_count") 

names(target_merge) <- targ_names 

weapon_merge  <- pres_terror %>% count(state_fips, county_fips, election_year, weaptype1_txt) %>% spread(weaptype1_txt, n, fill = 0)

weapon_names <- c("state_fips", "county_fips",  "election_year", "biological_weap_count","chemical_weap_count", "explosives_weap_count", "fake_weap_count", "firearms_weap_count", "incendiary_weap_count",
                  "melee_weap_count","other_weap_count", "radiological_weap_count","sabotage_weap_count", "unknown_weap_count", "vehicle_weap_count", "NA_weap_count") 

names(weapon_merge) <- weapon_names

pres_terror <- pres_terror %>% group_by(state_fips, county_fips, election_year, state_abbrev, TotalVote, dem_vote, rep_vote, other_vote, Margin, H, dem_perc_vote, rep_perc_vote, third_perc_vote, other_perc_vote, dem_raw_vote, rep_raw_vote, other_raw_vote) %>% 
  summarise(attack_count = sum(attack, na.rm = TRUE), days_till_election_avg = mean(days_till_election, na.rm = TRUE), days_till_election_last = min(days_till_election),
            abortion_count = sum(abortion, na.rm = TRUE), hatred_count = sum(hatred, na.rm = TRUE), political_count = sum(political, na.rm = TRUE), unknown_count = sum(unknown_motive, na.rm = TRUE),
            success_count = sum(success, na.rm = TRUE), vicinity_count = sum(vicinity, na.rm = TRUE), assassination_count = sum(attacktype1 == 1, na.rm = TRUE), armed_count = sum(attacktype1 == 2, na.rm = TRUE), 
            bomb_count = sum(attacktype1 == 3, na.rm = TRUE), hijack_count = sum(attacktype1 == 4, na.rm = TRUE), hostage_count = sum(attacktype1 == 5, na.rm = TRUE), kidnap_count = sum(attacktype1 == 6, na.rm = TRUE),
            facility_count = sum(attacktype1 == 7, na.rm = TRUE), unarmed_count = sum(attacktype1 == 8, na.rm = TRUE), unknown_type_count = sum(attacktype1 == 9, na.rm = TRUE), suicide_count = sum(suicide, na.rm = TRUE),
            nperps_avg = mean(as.integer(nperps), na.rm = TRUE), nperps_tot = sum(as.integer(nperps), na.rm = TRUE), claimed_count = sum(claimed, na.rm = TRUE), nkill_avg = mean(nkill, na.rm = TRUE), nkill_tot = sum(nkill, na.rm = TRUE),
            nkillter_avg = mean(nkillter, na.rm = TRUE), nkillter_tot = sum(nkillter, na.rm = TRUE), nwound_avg = mean(nwound, na.rm = TRUE), nwound_tot = sum(nwound, na.rm = TRUE), nwoundter_avg = mean(nwoundte, na.rm = TRUE),
            nwoundter_tot = sum(nwoundte, na.rm = TRUE), property_count = sum(property, na.rm = TRUE), propvalue_avg = mean(as.integer(propvalue), na.rm = TRUE), propvalue_tot = sum(as.integer(propvalue), na.rm = TRUE), 
            international_logistics_count = sum(INT_LOG, na.rm = TRUE), )

pres_terror <- merge(pres_terror, target_merge, by = c("state_fips","county_fips","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))
pres_terror <- merge(pres_terror, weapon_merge, by = c("state_fips","county_fips","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))


write.csv(pres_terror, "Merged_datasets\\pres_all_terror.csv")
pres_terror <- read.csv("Merged_datasets\\pres_all_terror.csv") %>% select(-X) %>% rename(totalvote = TotalVote, dem_rank = dem_vote, rep_rank = rep_vote, other_rank = other_vote, margin = Margin, perc_margin = H)
save.dta13(pres_terror, "Merged_datasets\\pres_all_terror.dta")


###############################################
############# Senate Data #####################
###############################################

######## read data
terror <- terror_base
senate <- read.dta13("Election_datasets\\Congressional Elections\\Senate\\Senate_merged_congress.dta") %>% unique()
########

######## Election Dates
senate$election_date[senate$year == 1994 ] <- "1994-11-08"
senate$election_date[senate$year == 1996 ] <- "1996-11-05"
senate$election_date[senate$year == 1998 ] <- "1998-11-03"
senate$election_date[senate$year == 2000 ] <- "2000-11-07"
senate$election_date[senate$year == 2002 ] <- "2002-11-05"
senate$election_date[senate$year == 2004 ] <- "2004-11-02"
senate$election_date[senate$year == 2006 ] <- "2006-11-07"
senate$election_date[senate$year == 2008 ] <- "2008-11-04"
senate$election_date[senate$year == 2010 ] <- "2010-11-02"
senate$election_date[senate$year == 2012 ] <- "2012-11-06"
senate$election_date[senate$year == 2014 ] <- "2014-11-04"
senate$election_date[senate$year == 2016 ] <- "2016-11-08"

senate$last_election[senate$year == 1994 ] <- "1992-11-03"
senate$last_election[senate$year == 1996 ] <- "1994-11-08"
senate$last_election[senate$year == 1998 ] <- "1996-11-05"
senate$last_election[senate$year == 2000 ] <- "1998-11-03"
senate$last_election[senate$year == 2002 ] <- "2000-11-07"
senate$last_election[senate$year == 2004 ] <- "2002-11-05"
senate$last_election[senate$year == 2006 ] <- "2004-11-02"
senate$last_election[senate$year == 2008 ] <- "2006-11-07"
senate$last_election[senate$year == 2010 ] <- "2008-11-04"
senate$last_election[senate$year == 2012 ] <- "2010-11-02"
senate$last_election[senate$year == 2014 ] <- "2012-11-06"
senate$last_election[senate$year == 2016 ] <- "2014-11-04"


senate$election_date <- as.Date(senate$election_date, format = "%Y-%m-%d")
senate <- unique(senate) %>% select(-c(prev_leg_elec_year, next_leg_elec_year)) %>% arrange(fips, year)
########

######## Terror Dates
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

terror <- terror %>% filter(year >= 1992) %>% rename(fips = FIPSCounty)
########

########
election_date <- c("1994-11-08","1996-11-05","1998-11-03","2000-11-07","2002-11-05","2004-11-02","2006-11-07","2008-11-04","2010-11-02","2012-11-06","2014-11-04","2016-11-08")

election_year <- c(1994,1996,1998,2000,2002,2004,2006,2008,2010,2012,2014,2016)

dates <- data.frame(election_year, election_date, country = 217)

dates$election_date <- as.Date(dates$election_date, format = "%Y-%m-%d")

terror$next_election <- neardate(terror$country, dates$country, terror$event_date, dates$election_date, best = "after", nomatch = NA)

dates$next_election <- seq.int(nrow(dates))

terror <- merge(terror, dates, by = "next_election", all.x = TRUE) %>% select(-c(election_date, country.y, next_election)) %>% rename(country = country.x, attack_year = year) %>%
  mutate(attack = 1, abortion = ifelse(motive1 == "Anti-Abortion", 1, 0), hatred = ifelse(motive1 == "Hatred", 1, 0), political = ifelse(motive1 == "Political", 1, 0), unknown_motive = ifelse(motive1 == "Unknown", 1, 0))
########

########
senate$election_year <- senate$year

senate_terror <- merge(senate, terror, by = c("state_fips","county_fips","election_year"), all.x = TRUE)  %>% mutate_all(~na_if(., -9)) %>% filter(event_date > last_election | is.na(event_date))


senate_terror$days_till_election <- senate_terror$election_date - senate_terror$event_date

#senate_terror$targtype1_txt[is.na(senate_terror$targtype1_txt)] <- "NA"

target_merge  <- senate_terror %>% ungroup() %>% count(fips.x, election_year, targtype1_txt) %>% spread(targtype1_txt, n, fill = 0)

targ_names <- c("fips.x","election_year","abortion_targ_count","air_targ_count","business_targ_count","education_targ_count","diplo_targ_count","gov_targ_count","media_targ_count","maritime_targ_count","military_targ_count","ngo_targ_count","other_targ_count","police_targ_count",                         
                "private_targ_count","relig_targ_count","tourist_targ_count","transport_targ_count","unknown_targ_count","utilities_targ_count","NA_targ_count") 

names(target_merge) <- targ_names 

weapon_merge  <- senate_terror %>% count(state_fips, county_fips, election_year, weaptype1_txt) %>% spread(weaptype1_txt, n, fill = 0)

weapon_names <- c("state_fips", "county_fips","election_year", "biological_weap_count","chemical_weap_count", "explosives_weap_count","fake_weap_count", "firearms_weap_count", "incendiary_weap_count", "melee_weap_count",
                  "other_weap_count","unknown_weap_count","vehicle_weap_count", "NA_weap_count") 

names(weapon_merge) <- weapon_names


senate_terror <- senate_terror %>% filter(fips.x != 23099) %>% group_by(state_fips, county_fips, election_year, county_name, state_abbrev, totalvote, demo_elecvote_s, repub_elecvotes_s, ind_elecvotes_s, margin, margin_pct_s, demo_pct_s, repub_pct_s, ind_pct_s, other_pct_s, dem, rep, fips.x) %>% 
  summarise(attack_count = sum(attack, na.rm = TRUE), days_till_election_avg = mean(days_till_election, na.rm = TRUE), days_till_election_last = min(days_till_election),
            abortion_count = sum(abortion, na.rm = TRUE), hatred_count = sum(hatred, na.rm = TRUE), political_count = sum(political, na.rm = TRUE), unknown_count = sum(unknown_motive, na.rm = TRUE),
            success_count = sum(success, na.rm = TRUE), vicinity_count = sum(vicinity, na.rm = TRUE), assassination_count = sum(attacktype1 == 1, na.rm = TRUE), armed_count = sum(attacktype1 == 2, na.rm = TRUE), 
            bomb_count = sum(attacktype1 == 3, na.rm = TRUE), hijack_count = sum(attacktype1 == 4, na.rm = TRUE), hostage_count = sum(attacktype1 == 5, na.rm = TRUE), kidnap_count = sum(attacktype1 == 6, na.rm = TRUE),
            facility_count = sum(attacktype1 == 7, na.rm = TRUE), unarmed_count = sum(attacktype1 == 8, na.rm = TRUE), unknown_type_count = sum(attacktype1 == 9, na.rm = TRUE), suicide_count = sum(suicide, na.rm = TRUE),
            nperps_avg = mean(as.integer(nperps), na.rm = TRUE), nperps_tot = sum(as.integer(nperps), na.rm = TRUE), claimed_count = sum(claimed, na.rm = TRUE), nkill_avg = mean(nkill, na.rm = TRUE), nkill_tot = sum(nkill, na.rm = TRUE),
            nkillter_avg = mean(nkillter, na.rm = TRUE), nkillter_tot = sum(nkillter, na.rm = TRUE), nwound_avg = mean(nwound, na.rm = TRUE), nwound_tot = sum(nwound, na.rm = TRUE), nwoundter_avg = mean(nwoundte, na.rm = TRUE),
            nwoundter_tot = sum(nwoundte, na.rm = TRUE), property_count = sum(property, na.rm = TRUE), propvalue_avg = mean(as.integer(propvalue), na.rm = TRUE), propvalue_tot = sum(as.integer(propvalue), na.rm = TRUE), 
            international_logistics_count = sum(INT_LOG, na.rm = TRUE))

senate_terror <- merge(senate_terror, target_merge, by = c("fips.x","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))

senate_terror <- merge(senate_terror, weapon_merge, by = c("state_fips","county_fips","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))


write.csv(senate_terror, "Merged_datasets\\senate_all_terror.csv")
senate_terror <- read.csv("Merged_datasets\\senate_all_terror.csv") %>% select(-X) %>% rename(fips = fips.x, dem_rank = demo_elecvote_s, rep_rank = repub_elecvotes_s, ind_rank = ind_elecvotes_s, perc_margin = margin_pct_s, dem_perc_vote = demo_pct_s, rep_perc_vote = repub_pct_s, ind_perc_vote = ind_pct_s, dem_raw_vote = dem, rep_raw_vote = rep)
save.dta13(senate_terror, "Merged_datasets\\senate_all_terror.dta")
########

###############################################
############# House Data #####################
###############################################

######## read data
terror <- terror_base
house <- read.dta13("Election_datasets\\Congressional Elections\\House\\House_merged_congress.dta") %>% mutate(fipsyr = paste0(fips, year)) %>% filter(fipsyr != 27022012, fipsyr != 27022014, fipsyr != 27032012, fipsyr != 27032014, fipsyr != 27401996) %>% unique() %>% select(-fipsyr)
########

######## Election Dates
house$election_date[house$year == 1994 ] <- "1994-11-08"
house$election_date[house$year == 1996 ] <- "1996-11-05"
house$election_date[house$year == 1998 ] <- "1998-11-03"
house$election_date[house$year == 2000 ] <- "2000-11-07"
house$election_date[house$year == 2002 ] <- "2002-11-05"
house$election_date[house$year == 2004 ] <- "2004-11-02"
house$election_date[house$year == 2006 ] <- "2006-11-07"
house$election_date[house$year == 2008 ] <- "2008-11-04"
house$election_date[house$year == 2010 ] <- "2010-11-02"
house$election_date[house$year == 2012 ] <- "2012-11-06"
house$election_date[house$year == 2014 ] <- "2014-11-04"
house$election_date[house$year == 2016 ] <- "2016-11-08"

house$last_election[house$year == 1994 ] <- "1992-11-03"
house$last_election[house$year == 1996 ] <- "1994-11-08"
house$last_election[house$year == 1998 ] <- "1996-11-05"
house$last_election[house$year == 2000 ] <- "1998-11-03"
house$last_election[house$year == 2002 ] <- "2000-11-07"
house$last_election[house$year == 2004 ] <- "2002-11-05"
house$last_election[house$year == 2006 ] <- "2004-11-02"
house$last_election[house$year == 2008 ] <- "2006-11-07"
house$last_election[house$year == 2010 ] <- "2008-11-04"
house$last_election[house$year == 2012 ] <- "2010-11-02"
house$last_election[house$year == 2014 ] <- "2012-11-06"
house$last_election[house$year == 2016 ] <- "2014-11-04"


house$election_date <- as.Date(house$election_date, format = "%Y-%m-%d")
house <- unique(house) %>% select(-c(prev_leg_elec_year, next_leg_elec_year)) %>% arrange(fips, year)
########

######## Terror Dates
terror$event_date <- as.Date(paste(terror$year,terror$imonth,terror$iday,sep="-"), format = "%Y-%m-%d")

terror <- terror %>% filter(year >= 1992) %>% rename(fips = FIPSCounty)
########

########
election_date <- c("1994-11-08","1996-11-05","1998-11-03","2000-11-07","2002-11-05","2004-11-02","2006-11-07","2008-11-04","2010-11-02","2012-11-06","2014-11-04","2016-11-08")

election_year <- c(1994,1996,1998,2000,2002,2004,2006,2008,2010,2012,2014,2016)

dates <- data.frame(election_year, election_date, country = 217)

dates$election_date <- as.Date(dates$election_date, format = "%Y-%m-%d")

terror$next_election <- neardate(terror$country, dates$country, terror$event_date, dates$election_date, best = "after", nomatch = NA)

dates$next_election <- seq.int(nrow(dates))

terror <- merge(terror, dates, by = "next_election", all.x = TRUE) %>% select(-c(election_date, country.y, next_election)) %>% rename(country = country.x, attack_year = year) %>%
  mutate(attack = 1, abortion = ifelse(motive1 == "Anti-Abortion", 1, 0), hatred = ifelse(motive1 == "Hatred", 1, 0), political = ifelse(motive1 == "Political", 1, 0), unknown_motive = ifelse(motive1 == "Unknown", 1, 0))
########

########
house$election_year <- house$year

house_terror <- merge(house, terror, by = c("state_fips","county_fips","election_year"), all.x = TRUE)  %>% mutate_all(~na_if(., -9)) %>% filter(event_date > last_election | is.na(event_date))


house_terror$days_till_election <- house_terror$election_date - house_terror$event_date

#house_terror$targtype1_txt[is.na(house_terror$targtype1_txt)] <- "NA"

target_merge  <- house_terror %>% ungroup() %>% count(fips.x, election_year, targtype1_txt) %>% spread(targtype1_txt, n, fill = 0)

targ_names <- c("fips.x","election_year","abortion_targ_count","air_targ_count","business_targ_count","education_targ_count","diplo_targ_count","gov_targ_count","media_targ_count","maritime_targ_count","military_targ_count","ngo_targ_count","other_targ_count",                         
                "police_targ_count","private_targ_count","relig_targ_count","nonstate_actor_targ_count","tourist_targ_count","transport_targ_count","unknown_targ_count","utilities_targ_count","violent_political_party_targ_count","NA_targ_count") 

names(target_merge) <- targ_names 

weapon_merge  <- house_terror %>% count(state_fips, county_fips, election_year, weaptype1_txt) %>% spread(weaptype1_txt, n, fill = 0)

weapon_names <- c("state_fips","county_fips","election_year","biological_weap_count","chemical_weap_count","explosives_weap_count","fake_weap_count",
                  "firearms_weap_count", "incendiary_weap_count","melee_weap_count","other_weap_count", "sabotage_weap_count","unknown_weap_count","vehicle_weap_count", "NA_weap_count") 

names(weapon_merge) <- weapon_names

########
house_terror <- house_terror %>% filter(fips.x != 23099) %>% group_by(state_fips, county_fips, election_year, county_name, state_abbrev, totalvote, demo_elecvote_h, repub_elecvotes_h, ind_elecvotes_h, margin, margin_pct_h, demo_pct_h, repub_pct_h, ind_pct_h, other_pct_h, dem, rep, cd, fips.x) %>% 
  summarise(attack_count = sum(attack, na.rm = TRUE), days_till_election_avg = mean(days_till_election, na.rm = TRUE), days_till_election_last = min(days_till_election),
            abortion_count = sum(abortion, na.rm = TRUE), hatred_count = sum(hatred, na.rm = TRUE), political_count = sum(political, na.rm = TRUE), unknown_count = sum(unknown_motive, na.rm = TRUE),
            success_count = sum(success, na.rm = TRUE), vicinity_count = sum(vicinity, na.rm = TRUE), assassination_count = sum(attacktype1 == 1, na.rm = TRUE), armed_count = sum(attacktype1 == 2, na.rm = TRUE), 
            bomb_count = sum(attacktype1 == 3, na.rm = TRUE), hijack_count = sum(attacktype1 == 4, na.rm = TRUE), hostage_count = sum(attacktype1 == 5, na.rm = TRUE), kidnap_count = sum(attacktype1 == 6, na.rm = TRUE),
            facility_count = sum(attacktype1 == 7, na.rm = TRUE), unarmed_count = sum(attacktype1 == 8, na.rm = TRUE), unknown_type_count = sum(attacktype1 == 9, na.rm = TRUE), suicide_count = sum(suicide, na.rm = TRUE),
            nperps_avg = mean(as.integer(nperps), na.rm = TRUE), nperps_tot = sum(as.integer(nperps), na.rm = TRUE), claimed_count = sum(claimed, na.rm = TRUE), nkill_avg = mean(nkill, na.rm = TRUE), nkill_tot = sum(nkill, na.rm = TRUE),
            nkillter_avg = mean(nkillter, na.rm = TRUE), nkillter_tot = sum(nkillter, na.rm = TRUE), nwound_avg = mean(nwound, na.rm = TRUE), nwound_tot = sum(nwound, na.rm = TRUE), nwoundter_avg = mean(nwoundte, na.rm = TRUE),
            nwoundter_tot = sum(nwoundte, na.rm = TRUE), property_count = sum(property, na.rm = TRUE), propvalue_avg = mean(as.integer(propvalue), na.rm = TRUE), propvalue_tot = sum(as.integer(propvalue), na.rm = TRUE), 
            international_logistics_count = sum(INT_LOG, na.rm = TRUE))

house_terror <- merge(house_terror, target_merge, by = c("fips.x","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))

house_terror <- merge(house_terror, weapon_merge, by = c("state_fips","county_fips","election_year"), all.x = TRUE) %>% mutate_all(~na_if(., 'NaN'))


write.csv(house_terror, "Merged_datasets\\house_all_terror.csv")
house_terror <- read.csv("Merged_datasets\\house_all_terror.csv") %>% select(-X) %>% rename(fips = fips.x, dem_rank = demo_elecvote_h, rep_rank = repub_elecvotes_h, ind_rank = ind_elecvotes_h, perc_margin = margin_pct_h, dem_perc_vote = demo_pct_h, rep_perc_vote = repub_pct_h, ind_perc_vote = ind_pct_h, other_perc_vote = other_pct_h, dem_raw_vote = dem, rep_raw_vote = rep)
save.dta13(house_terror, "Merged_datasets\\house_all_terror.dta")
